package com.md.autonew;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import org.springframework.core.io.ClassPathResource;

import com.md.exception.SystemException;
import com.md.model.Entity;
import com.md.model.Property;

/**
 * 
 ********************************************************* .<br>
 * [类名] ReadTable <br>
 * [描述] 自动生产相关的类 <br>
 * [作者] yehao <br>
 ********************************************************* .<br>
 */
@SuppressWarnings("all")
public class ReadTable {

	private String DRIVER = "";
	private String URL = "";
	private String USENAME = "";
	private String USEPASS = "";

	/**
	 * 获取连接
	 */
	public Connection getConn() {

		ClassPathResource cr = new ClassPathResource("dataSource.properties");// 会重新加载spring框架
		Properties pros = new Properties();
		try {
			pros.load(cr.getInputStream());
		} catch (IOException ex) {
		}

		DRIVER = pros.get("driver").toString();
		URL = pros.get("url").toString();
		USENAME = pros.get("username").toString();
		USEPASS = pros.get("password").toString();

		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USENAME, USEPASS);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}
		return conn;
	}

	/**
	 * Read from table to entity.
	 * 
	 * @author yehao
	 * @param table
	 */
	public Entity readEntity(String table) {
		Entity entity = new Entity(table);
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		ResultSetMetaData rsmd = null;
		String findsql = "select * from " + table;
		try {
			pstmt = conn.prepareStatement(findsql);
			pstmt.execute(); // 这点特别要注意:如果是Oracle 而对于mysql可以不用加.
			rsmd = pstmt.getMetaData();
			Property property = null;
			ResultSet primaryKeys = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), conn.getSchema(), table);
			while (primaryKeys.next()) {
				property = new Property();
				property.setName(primaryKeys.getString("COLUMN_NAME").toLowerCase());
				property.setPrimaryKey(true);
			}
			entity.addProperty(property);
			for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
				property = new Property();
				property.setName(rsmd.getColumnName(i).toLowerCase()); // id
				property.setJavaClass(rsmd.getColumnClassName(i)
						.substring(rsmd.getColumnClassName(i).lastIndexOf(".") + 1, rsmd.getColumnClassName(i).length())); // java.lang.String
				entity.addProperty(property);
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return entity;
		}
	}

	/**
	 * ********************************************************
	 * 
	 * @Title: read
	 * @Description: 获取表字段名和数据类型
	 * @param table
	 * @return Map
	 ********************************************************
	 */
	public Map read(String table) {

		Map<String, String[][]> map = new HashMap<String, String[][]>();
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		ResultSetMetaData rsmd = null;
		String findsql = "select * from " + table;
		try {
			pstmt = conn.prepareStatement(findsql);
			pstmt.execute(); // 这点特别要注意:如果是Oracle 而对于mysql可以不用加.
			rsmd = (ResultSetMetaData) pstmt.getMetaData();

			for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
				map.put(rsmd.getColumnName(i).toLowerCase(), new String[][] { { rsmd.getColumnTypeName(i).toLowerCase(), rsmd.getScale(i) + "" } });
			}

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {

			}
		}
		return map;
	}

	/**
	 * ********************************************************
	 * 
	 * @Title: findDecimals
	 * @Description: 查询小数位数
	 * @param table
	 * @param attribute
	 * @return boolean
	 ********************************************************
	 */
	public int findDecimals(String table, String attribute) {

		String sql = "";
		if (DRIVER.toUpperCase().indexOf("ORACLE") >= 0) {
			sql = "SELECT nvl(data_scale,0) FROM ALL_TAB_COLUMNS WHERE OWNER= (select default_tablespace from user_users) and data_type = 'NUMBER' and table_name = '"
					+ table.toUpperCase() + "'  and column_name = '" + attribute.toUpperCase() + "' "; // ORACLE
		} else if (DRIVER.toUpperCase().indexOf("MYSQL") >= 0) {
			sql = "select ifnull(numeric_scale,0) from information_schema.columns where table_schema = (SELECT database()) and table_name = '" + table
					+ "' and column_name = '" + attribute + "'"; // MYSQL
		}

		Connection conn = getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int num = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return num;
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {

			}
		}
		return num;
	}

	/**
	 * ********************************************************
	 * 
	 * @Title: findTableComments
	 * @Description: 查询 Oracle 数据库 Comments 表说明
	 * @param table
	 *            表名
	 * @return String
	 ********************************************************
	 */
	public String findTableComments(String table) {
		String sql = "";
		if (DRIVER.toUpperCase().indexOf("ORACLE") >= 0) {
			sql = "select nvl(comments,'') from user_tab_comments where table_name='" + table.toUpperCase() + "'"; // ORACLE
		} else if (DRIVER.toUpperCase().indexOf("MYSQL") >= 0) {
			sql = "SELECT substring_index(table_comment,';',1) from Information_schema.TABLES where table_schema = (SELECT database()) and  table_name = '"
					+ table + "'"; // MYSQL
		}
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String comments = "";
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				comments = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {

			}
		}
		return comments;
	}

	/**
	 * ********************************************************
	 * 
	 * @Title: findColComment
	 * @Description: 查询该表所有属性的说明
	 * @param table
	 * @return Map
	 ********************************************************
	 */
	public Map findColComment(String table) {
		Map<String, String> map = new HashMap<String, String>();
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String findsql = "";
		if (DRIVER.toUpperCase().indexOf("ORACLE") >= 0) {
			findsql = "select column_name,nvl(comments,'') from user_col_comments where table_name='" + table.toUpperCase() + "'"; // ORACLE
		} else if (DRIVER.toUpperCase().indexOf("MYSQL") >= 0) {
			findsql = "select column_name,ifnull(column_comment,'') from information_schema.columns where table_schema = (SELECT database()) and table_name = '"
					+ table + "'"; // MYSQL
		}

		try {
			pstmt = conn.prepareStatement(findsql);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				map.put(rs.getString(1).toLowerCase(), rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {

			}
		}
		return map;
	}
}
